/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.swing.JOptionPane;

import com.mysql.jdbc.Statement;

import Agenda.Contato;
import Agenda.CriaConexao;
import Agenda.SingletonConexao;

/**
 *
 * @author sanford
 */
public class ContatoDao {

	private Connection conexao;

	public ContatoDao() throws SQLException, ClassNotFoundException {
		CriaConexao.createDb();
		conexao = SingletonConexao.getInstance().getConexao();
		Statement stmt = (Statement) conexao.createStatement();
		stmt.execute("use agendadb;");
	}

	public void adiciona(Contato c1) throws SQLException {
		String sql = "insert into contato (nome,endereco,email,sexo,telefone)"
				+ "values(?,?,?,?,?)";
		try (PreparedStatement stmt = conexao.prepareStatement(sql)) {
			stmt.setString(1, c1.getNome());
			stmt.setString(2, c1.getEndereco());
			stmt.setString(3, c1.getEmail());
			stmt.setString(4, c1.getSexo());
			stmt.setString(5, c1.getTel());

			stmt.execute();
			stmt.close();
		}
	}

	public List<Contato> getLista() throws SQLException {
		String sql = "select * from contato";
		List<Contato> lContatos;
		try (PreparedStatement stmt = conexao.prepareStatement(sql); 
				ResultSet rs = stmt.executeQuery()) {
			lContatos = new ArrayList<>();
			while (rs.next()) {
				Contato c1 = new Contato();
				c1.setNome(rs.getString("nome"));
				c1.setEmail(rs.getString("email"));
				c1.setEndereco(rs.getString("endereco"));
				c1.setSexo(rs.getString("sexo"));
				c1.setTel(rs.getString("telefone"));
				lContatos.add(c1);

			}
		}
		return lContatos;
	}
	public List<Contato> getLista(String nome) throws SQLException {
		String sql = "select * from contato where nome like ?";
		List<Contato> lContatos;
		PreparedStatement stmt = conexao.prepareStatement(sql);
		stmt.setString(1, nome);
		ResultSet rs = stmt.executeQuery();
		lContatos = new ArrayList<>();
		while (rs.next()) {
			Contato c1 = new Contato();
			c1.setId(Long.valueOf(rs.getString("id")));
			c1.setNome(rs.getString("nome"));
			c1.setEmail(rs.getString("email"));
			c1.setEndereco(rs.getString("endereco"));
			c1.setSexo(rs.getString("sexo"));
			c1.setTel(rs.getString("telefone"));
			lContatos.add(c1);

		}

		return lContatos;
	}

	public void altera(Contato c2) throws SQLException{
		String sql = "update contato set nome=?, endereco=?, telefone=?, email=?,"+
				"sexo=? where id = ?";
		try (PreparedStatement stmt = conexao.prepareStatement(sql)) {
			stmt.setString(1, c2.getNome());
			stmt.setString(2, c2.getEndereco());
			stmt.setString(3, c2.getTel());
			stmt.setString(4, c2.getEmail());
			stmt.setString(5, c2.getSexo());
			stmt.setString(6, c2.getId().toString());

			stmt.execute();
			stmt.close();
		}


	}

	public void remove(Contato c1) throws SQLException{
		String sql = "delete from contato where id=?";
		PreparedStatement stmt = conexao.prepareStatement(sql);
		stmt.setLong(1, c1.getId());
		stmt.execute();
		stmt.close();
		JOptionPane.showMessageDialog(null, "O contato " +c1.getNome() + " foi "
				+ "excluido com sucesso!");
	}

}
